package atguigu.excel.ricer;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

public class RicerReport {


    public static void test1(){
        Workbook workbook = null;
        try {
            InputStream in = getModel();
            workbook = new XSSFWorkbook(in);
            XSSFSheet sheet0 = (XSSFSheet) workbook.getSheetAt(11);
            sheet0.setForceFormulaRecalculation(true);
            int index = 0;
            for (int i = 0; i < 3; i++) {
                writeACell(sheet0,4+index,3,"111");
                index +=1;
            }
            workbook.write(new FileOutputStream(new File("d:/IH电磁加热电脑饭煲整机综合评价报告1.xlsx")));
            System.out.println("写完");
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void test2(){
        Workbook workbook = null;
        try {
            InputStream in = getModel();
            workbook = new XSSFWorkbook(in);

            List<String> sheetNames = new ArrayList<>();
            for (int i = 4; i < 10; i++) {
                Sheet sheetAt = workbook.getSheetAt(i);
                String sheetName = sheetAt.getSheetName();
                sheetNames.add(sheetName);
            }

            XSSFSheet sheet0 = (XSSFSheet) workbook.getSheetAt(3);
            sheet0.setForceFormulaRecalculation(true);

            // 创建 XSSFCreationHelper 对象
            CreationHelper helper = workbook.getCreationHelper();
            for (int i = 0; i < sheetNames.size(); i++) {
                XSSFHyperlink hyperlink = (XSSFHyperlink) helper.createHyperlink(HyperlinkType.URL);
                String sheetName = sheetNames.get(i).trim();
                hyperlink.setAddress("sheet:///"+sheetName);
                hyperlink.setLabel("跳转到:"+sheetName);
                writeASheetLinkCell(sheet0,2+i,0,sheetName,hyperlink);
            }
            workbook.write(new FileOutputStream(new File("d:/IH电磁加热电脑饭煲整机综合评价报告1.xlsx")));
            System.out.println("写完");
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void test3(){

        Workbook workbook = null;
        try {
            InputStream in = new FileInputStream(new File("d:/exce/继电器测试报告表单化模板.xlsx"));
            workbook = new XSSFWorkbook(in);
            XSSFSheet sheet0 = (XSSFSheet) workbook.getSheetAt(0);
            sheet0.setForceFormulaRecalculation(true);
            XSSFRow row = sheet0.getRow(8);
            if(row == null) row = sheet0.createRow(8);
            XSSFCell cell = row.getCell(3);
            if(cell == null) row.createCell(3);



            // 创建 XSSFCreationHelper 对象
            CreationHelper helper = workbook.getCreationHelper();


            XSSFHyperlink hyperlink = (XSSFHyperlink) helper.createHyperlink(HyperlinkType.FILE);
            String filePath = "file://" + "d:" + File.separator + "exce" + File.separator + "数据银行_整机明细口径确认V1108.xlsx";
            hyperlink.setAddress(filePath);
            hyperlink.setLabel("跳转到:数据银行_整机明细口径确认V1108 (1).xlsx");
            cell.setHyperlink(hyperlink);

            workbook.write(new FileOutputStream(new File("d:/exce/继电器测试报告表单化模板1.xlsx")));
            System.out.println("写完");
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void main(String[] args) {

        test3();
    }


    public static InputStream getModel() {
        return RicerReport.class.getResourceAsStream(
               getReportTemplateFilePath("IH电磁加热电脑饭煲整机综合评价报告.xlsx")
        );
    }

    public static String getReportTemplateFilePath(String fileName){
        StringBuffer realPath = new StringBuffer("/");
        realPath.append("template");
        realPath.append("/");
        realPath.append(fileName);
        return realPath.toString();
    }



    public static void writeACell(XSSFSheet sheet0, int r, int c, String strValue) {
        if(StringUtils.isNotBlank(strValue)){
            XSSFRow row = sheet0.getRow(r);
            if(row == null) row = sheet0.createRow(r);
            XSSFCell cell = row.getCell(c);
            if(cell == null) cell = row.createCell(c);
            try{
                double v = Double.parseDouble(strValue);
                cell.setCellValue(v);
            }catch (Exception e){
                cell.setCellValue(strValue);
            }
        }
    }

    public static void writeASheetLinkCell(XSSFSheet sheet0, int r, int c, String strValue,XSSFHyperlink link) {
        if(StringUtils.isNotBlank(strValue)){
            XSSFRow row = sheet0.getRow(r);
            if(row == null) row = sheet0.createRow(r);
            XSSFCell cell = row.getCell(c);
            if(cell == null) cell = row.createCell(c);
            try{
                double v = Double.parseDouble(strValue);
                cell.setCellValue(v);
            }catch (Exception e){
                cell.setCellValue(strValue);
            }
            cell.setHyperlink(link);

        }
    }
}
